/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package br.com.caelum.jdbc.dao;

import br.com.caelum.jdbc.*;
import br.com.caelum.jdbc.modelo.Contato;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.ArrayList;

/**
 * @author menandro.neto
 */
public class ContatoDAO {

    private Connection connection;
    
    public ContatoDAO() throws SQLException{
        //connection = new MySQLTest().getConnection();
        connection = new FBTest().getConnection();
    }
    
    public void adiciona(Contato contato) throws SQLException{
        String sqlInsert = "insert into contatos(cntNome, cntEmail, cntEndereco) " +
                           "values (?, ?, ?)"; 
        PreparedStatement pstm = connection.prepareStatement(sqlInsert);
        
        pstm.setString(1, contato.getCntNome());
        pstm.setString(2, contato.getCntEmail());
        pstm.setString(3, contato.getCntEndereco());

        pstm.execute();
        pstm.close();
        connection.close();        
    }
    
    public List<Contato> getAllContatos() throws SQLException{
        String sqlSelect = "select cntNome, cntEmail from contatos";
        PreparedStatement pstm = this.connection.prepareStatement(sqlSelect);
        ResultSet rs = pstm.executeQuery();
        
        List<Contato> contatos = new ArrayList<Contato>();
        
        while ( rs.next() ) {
            contatos.add(new Contato(0, rs.getString("cntNome"), rs.getString("cntEmail"), "") );
        }
        
        return (contatos);
    }
    
    public List<Contato> getContatosByNome(String criteria) throws SQLException {
        String sqlSelect = "select contatosId, cntNome, cntEmail from contatos where cntNome like ?";
        PreparedStatement pstm = this.connection.prepareStatement(sqlSelect);
        pstm.setString(1, criteria);
        ResultSet rs = pstm.executeQuery();
        
        List<Contato> contatos = new ArrayList<Contato>();
        
        while ( rs.next() ) {
            contatos.add(new Contato(rs.getLong("contatosId"), 
                                     rs.getString("cntNome"), 
                                     rs.getString("cntEmail"), "") );
        }
        
        return (contatos);
    }
    
    public Contato getContatoById( Long id) throws SQLException {
        String sqlSelect = "select cntNome, cntEmail from contatos where contatosId = ?";
        PreparedStatement pstm = this.connection.prepareStatement(sqlSelect);
        pstm.setLong(1, id);
        ResultSet rs = pstm.executeQuery();

        if ( rs.next() ) {
            return( new Contato(0, rs.getString("cntNome"), rs.getString("cntEmail"), "") );
        }
        
        return (null);
    }
    
}
